package ua.kharkov.knure.mikhaylov.st4.db.management;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import ua.kharkov.knure.mikhaylov.st4.db.entities.Account;
import ua.kharkov.knure.mikhaylov.st4.db.entities.Card;
import ua.kharkov.knure.mikhaylov.st4.db.entities.Payment;
import ua.kharkov.knure.mikhaylov.st4.db.entities.User;
import ua.kharkov.knure.mikhaylov.st4.db.entities.Valuta;
import ua.kharkov.knure.mikhaylov.st4.db.enums.AccountState;
import ua.kharkov.knure.mikhaylov.st4.db.enums.PaymentState;
import ua.kharkov.knure.mikhaylov.st4.db.enums.Role;
import ua.kharkov.knure.mikhaylov.st4.settings.Field;
import ua.kharkov.knure.mikhaylov.st4.settings.Query;

/**
 * Provides work with database via connection pool
 * 
 * @author Saint
 * 
 */
public class DBManager {

	private static final Logger log = Logger.getLogger(DBManager.class);

	/**
	 * Singleton instance
	 */
	private static DBManager instance;

	/**
	 * Singleton getInstance
	 * @return singleton instance
	 */
	public static DBManager getInstance() {
		if (instance == null)
			synchronized (DBManager.class) {
				if (instance == null)
					instance = new DBManager();
			}
		return instance;
	}

	/**
	 * Establishing connection
	 * 
	 * @return connection
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		Connection con = null;
		try {
			Context initContext = new InitialContext();
			Context envContext = (Context) initContext.lookup("java:/comp/env");

			DataSource ds = (DataSource) envContext.lookup("jdbc/ST4DB");
			con = ds.getConnection();
		} catch (NamingException ex) {
			log.error("Cannot obtain a connection from the pool", ex);
		}
		return con;
	}

	/**
	 * Finds user in database
	 * 
	 * @param id
	 *            - User id
	 * @return found user, null if not found
	 */
	public User findUserById(long id) {
		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.USER_FIND_BY_ID);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next())
				user = Extractor.getInstance().extractUser(rs);
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return user;
	}

	/**
	 * Finds user in database
	 * 
	 * @param login
	 *            - User login
	 * @return found user, null if not found
	 */
	public User findUserByLogin(String login) {
		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.USER_FIND_BY_LOGIN);
			pstmt.setString(1, login);
			rs = pstmt.executeQuery();
			if (rs.next())
				user = Extractor.getInstance().extractUser(rs);
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return user;
	}

	/**
	 * Finds all users in database. Note: do not use
	 * 
	 * @return users list
	 */
	@Deprecated
	public List<User> findAllUsers() {
		List<User> users = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.USER_FIND_ALL);
			rs = pstmt.executeQuery();
			users = Extractor.getInstance().extractAllUsers(rs);
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return users;
	}

	/**
	 * Finds users in database by role excepting one
	 * 
	 * @param role
	 *            - searching role
	 * @param user
	 *            - excluded user
	 * @return users list
	 */
	public List<User> findUsersByRoleExceptOne(Role role, User user) {
		List<User> users = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.USER_FIND_BY_ROLE_EXCEPT_ID);
			pstmt.setInt(1, role.ordinal());
			pstmt.setLong(2, user.getId());
			rs = pstmt.executeQuery();
			users = Extractor.getInstance().extractAllUsers(rs);
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return users;
	}

	/**
	 * Finds all valutas in database
	 * 
	 * @return valutas list
	 */
	public List<Valuta> findAllValutas() {
		List<Valuta> valutas = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.VALUTA_FIND_ALL);
			rs = pstmt.executeQuery();
			valutas = Extractor.getInstance().extractAllValutas(rs);
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return valutas;
	}

	/**
	 * Finds an account in database by it's id
	 * 
	 * @param id
	 *            - Account id
	 * @return found account, null if not found
	 */
	public Account findAccountById(long id) {
		Account account = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_FIND_BY_ID);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				account = Extractor.getInstance().extractAccount(rs);
				account.setUser(getInstance().findUserById(
						rs.getLong(Field.ACCOUNT_USER_ID)));
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return account;
	}

	/**
	 * Finds account in database by it's id. Sets an account user as a user
	 * param
	 * 
	 * @param id
	 *            - account id
	 * @param user
	 *            - user, which this account belongs to
	 * @return found account, null if not found
	 */
	public Account findAccountById(long id, User user) {
		Account account = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_FIND_BY_ID);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				account = Extractor.getInstance().extractAccount(rs);
				account.setUser(user);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return account;
	}

	/**
	 * Finds all accounts of selected user
	 * 
	 * @param user
	 *            - selected user
	 * @return accounts list
	 */
	public List<Account> findAccountsByUser(User user) {
		List<Account> accounts = new ArrayList<Account>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_FIND_ALL_BY_USER_ID);
			pstmt.setLong(1, user.getId());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Account account = Extractor.getInstance().extractAccount(rs);
				account.setUser(user);
				accounts.add(account);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return accounts;
	}

	/**
	 * Finds all open accounts by selected user
	 * 
	 * @param user
	 *            - selected user
	 * @return accounts list
	 */
	public List<Account> findOpenAccountsByUser(User user) {
		List<Account> accounts = new ArrayList<Account>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_FIND_OPEN_BY_USER_ID);
			pstmt.setLong(1, user.getId());
			pstmt.setInt(2, AccountState.OPEN.ordinal());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Account account = Extractor.getInstance().extractAccount(rs);
				account.setUser(user);
				accounts.add(account);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return accounts;
	}

	/**
	 * Finds all payments of selected user
	 * 
	 * @param user
	 *            - selected user
	 * @return payments list
	 */
	public List<Payment> findPaymentsByUser(User user) {
		List<Payment> payments = new ArrayList<Payment>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.PAYMENT_FIND_ALL_BY_USER_ID);
			pstmt.setLong(1, user.getId());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Payment payment = Extractor.getInstance().extractPayment(rs);
				payment.setPayer(getInstance().findAccountById(
						rs.getLong(Field.PAYMENT_PAYER_ID)));
				payment.setReciever(getInstance().findAccountById(
						rs.getLong(Field.PAYMENT_RECIEVER_ID)));
				payments.add(payment);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return payments;
	}

	/**
	 * Finds all accounts of selected user with selected valuta type
	 * 
	 * @param user
	 *            - selected user
	 * @param valuta
	 *            - selected valuta type
	 * @return accounts list
	 */
	public List<Account> findAccountsByUserAndValuta(User user, Valuta valuta) {
		List<Account> accounts = new ArrayList<Account>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con
					.prepareStatement(Query.ACCOUNT_FIND_ALL_BY_USER_ID_AND_VALUTA);
			pstmt.setLong(1, user.getId());
			pstmt.setLong(2, valuta.getId());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Account account = Extractor.getInstance().extractAccount(rs);
				account.setUser(user);
				accounts.add(account);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return accounts;
	}

	/**
	 * Finds all open accounts of selected user with selected valuta type
	 * 
	 * @param user
	 *            - selected user
	 * @param valuta
	 *            - selected valuta type
	 * @return accounts list
	 */
	public List<Account> findOpenAccountsByUserAndValuta(User user,
			Valuta valuta) {
		List<Account> accounts = new ArrayList<Account>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con
					.prepareStatement(Query.ACCOUNT_FIND_OPEN_BY_USER_ID_AND_VALUTA);
			pstmt.setLong(1, user.getId());
			pstmt.setLong(2, valuta.getId());
			pstmt.setInt(3, AccountState.OPEN.ordinal());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Account account = Extractor.getInstance().extractAccount(rs);
				account.setUser(user);
				accounts.add(account);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return accounts;
	}

	/**
	 * Finds all blocked accounts in database
	 * 
	 * @return blocked accounts list
	 */
	public List<Account> findAccountsBlocked() {
		List<Account> accounts = new ArrayList<Account>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_FIND_ALL_BLOCKED);
			pstmt.setLong(1, AccountState.CLOSED.ordinal());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Account account = Extractor.getInstance().extractAccount(rs);
				account.setUser(getInstance().findUserById(
						rs.getInt(Field.ACCOUNT_USER_ID)));
				accounts.add(account);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return accounts;
	}

	/**
	 * Finds all open accounts
	 * 
	 * @return accounts list
	 */
	public List<Account> findAccountsOpen() {
		List<Account> accounts = new ArrayList<Account>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_FIND_ALL_BLOCKED);
			pstmt.setLong(1, AccountState.OPEN.ordinal());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Account account = Extractor.getInstance().extractAccount(rs);
				account.setUser(getInstance().findUserById(
						rs.getInt(Field.ACCOUNT_USER_ID)));
				accounts.add(account);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return accounts;
	}

	/**
	 * Finds all credit cards by selected account
	 * 
	 * @param account
	 *            - selected account
	 * @return cards list
	 */
	public List<Card> findCardsByAccount(Account account) {
		List<Card> cards = new ArrayList<Card>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.CARD_FIND_ALL_BY_ACCOUNT_ID);
			pstmt.setLong(1, account.getId());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Card card = Extractor.getInstance().extractCard(rs);
				card.setAccount(account);
				cards.add(card);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return cards;
	}

	/**
	 * Finds payment by it's id
	 * 
	 * @param id
	 *            - payment id
	 * @return payment, null if not found
	 */
	public Payment findPaymentById(long id) {
		Payment payment = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.PAYMENT_FIND_BY_ID);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				payment = Extractor.getInstance().extractPayment(rs);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return payment;
	}

	/**
	 * Adds a new user to database
	 * 
	 * @param user
	 *            - new user
	 */
	public void insertUser(User user) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.USER_INSERT);
			pstmt.setString(1, user.getLogin());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getFirstName());
			pstmt.setString(4, user.getLastName());
			pstmt.setInt(5, user.getRole().ordinal());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Adds a new account to database
	 * 
	 * @param account
	 *            - new account
	 */
	public void insertAccount(Account account) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_CREATE_NEW);
			pstmt.setDouble(1, account.getBalance());
			pstmt.setLong(2, account.getValuta().getId());
			pstmt.setLong(3, account.getUser().getId());
			pstmt.setInt(4, account.getAccountState().ordinal());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Adds a new credit card to database
	 * 
	 * @param card
	 *            - new card
	 */
	public void insertCard(Card card) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.CARD_CREATE_NEW);
			pstmt.setInt(1, card.getNumber());
			pstmt.setInt(2, card.getSecurityCode());
			pstmt.setLong(3, card.getAccount().getId());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Adds a new payment to database
	 * 
	 * @param payment
	 *            - new payment
	 */
	public void insertPayment(Payment payment) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.PAYMENT_PREPARE);
			pstmt.setLong(1, payment.getPayer().getId());
			pstmt.setLong(2, payment.getReciever().getId());
			pstmt.setDouble(3, payment.getVal());
			pstmt.setDate(4, payment.getPaymentDate());
			pstmt.setInt(5, payment.getPaymentState().ordinal());
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Updates existing user in database
	 * 
	 * @param user
	 *            - existing user
	 */
	public void updateUser(User user) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.USER_UPDATE);
			pstmt.setString(1, user.getLogin());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getFirstName());
			pstmt.setString(4, user.getLastName());
			pstmt.setString(5, user.getRole().ordinal() + "");
			pstmt.setString(6, user.getId() + "");
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Updates account state of selected account
	 * 
	 * @param accountState
	 *            - new account state
	 * @param id
	 *            - selected account id
	 */
	public void updateAccountState(AccountState accountState, long id) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_UPDATE_STATE);
			pstmt.setLong(1, accountState.ordinal());
			pstmt.setLong(2, id);
			pstmt.executeUpdate();

			// closing open payments
			if (accountState == AccountState.CLOSED) {
				pstmt = con
						.prepareStatement(Query.PAYMENT_SET_STATE_BY_ACCOUNT_ID);
				pstmt.setInt(1, PaymentState.IGNORED.ordinal());
				pstmt.setLong(2, id);
				pstmt.setLong(3, id);
				pstmt.setInt(4, PaymentState.OPEN.ordinal());
				pstmt.executeUpdate();
			}

			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Updates selected account balance
	 * 
	 * @param value
	 *            - new balance value
	 * @param id
	 *            - selected account id
	 */
	public void updateAccountBalance(Double value, long id) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.ACCOUNT_UPDATE_BALANCE);
			pstmt.setDouble(1, value);
			pstmt.setLong(2, id);
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Reject selected payment
	 * 
	 * @param id
	 *            - selected payment id
	 */
	public void updatePaymentReject(long id) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.PAYMENT_SET_STATE_BY_ID);
			pstmt.setInt(1, PaymentState.IGNORED.ordinal());
			pstmt.setLong(2, id);
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Approove selected payment
	 * 
	 * @param id
	 *            - selected payment id
	 * @return true if payment has been approved, false if there's not enough
	 *         money for approovement
	 */
	public boolean updatePaymentApproove(long id) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();

			Payment payment = findPaymentById(id);
			Account payer = payment.getPayer();
			Account receiver = payment.getReciever();

			if (payer.getBalance() - payment.getVal() < 0) {
				rollbackAndClose(con);
				return false;
			}

			// anything else we have to do with a single transaction

			pstmt = con.prepareStatement(Query.ACCOUNT_UPDATE_BALANCE);
			pstmt.setDouble(1, payer.getBalance() - payment.getVal());
			pstmt.setLong(2, payer.getId());
			pstmt.executeUpdate();

			pstmt = con.prepareStatement(Query.ACCOUNT_UPDATE_BALANCE);
			pstmt.setDouble(1, receiver.getBalance() + payment.getVal());
			pstmt.setLong(2, receiver.getId());
			pstmt.executeUpdate();

			pstmt = con.prepareStatement(Query.PAYMENT_SET_STATE_BY_ID);
			pstmt.setInt(1, PaymentState.PAID.ordinal());
			pstmt.setLong(2, id);
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return true;
	}

	/**
	 * Deletes selected credit card from database
	 * @param id - selected credit card id
	 */
	public void deleteCardById(long id) {
		PreparedStatement pstmt = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.CARD_DELETE_BY_ID);
			pstmt.setLong(1, id);
			pstmt.executeUpdate();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
	}

	/**
	 * Counts user's open for approovement payments
	 * @param user - selected user
	 * @return number of open for approovement payments
	 */
	public int countOpenPaymentsByUser(User user) {
		int count = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement(Query.PAYMENT_COUNT_BY_USER);
			pstmt.setLong(1, user.getId());
			pstmt.setInt(2, PaymentState.OPEN.ordinal());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			rollbackAndClose(con);
			ex.printStackTrace();
		} finally {
			commitAndClose(con);
		}
		return count;
	}

	/**
	 * Commits transaction and closes connection
	 * @param con - affecting connection
	 */
	private void commitAndClose(Connection con) {
		try {
			con.commit();
			con.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Rollbacks transaction and closes connection
	 * @param con - affecting connection
	 */
	private void rollbackAndClose(Connection con) {
		try {
			con.rollback();
			con.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	
	/**
	 * Part of singleton
	 */
	private DBManager() {
	}

}